Resolution Levels
All analysis results from Engine are relative to a table. This table is referred to as the Resolution Level of the analytic operation. This section introduces the concept of a resolution table and how it affects analysis results.
The Resolution level is the level at which results are counted or summarized. This is also the Owner Table of a:
- Crosstab
- Domain
- Query
- Aggregate
Crosstab
The resolution table of a crosstab determines the level at which the crosstab counts are generated and therefore determines what each count in a cell refers to.
For example, if a crosstab is created at the CUSTOMER level (i.e. the crosstab is created from the Customer table) the counts in the cells refer to counts of customers. Likewise, if the crosstab is created at the transaction level, the counts refer to counts of transactions.
Example
A Crosstab is created from the Customer table. [Customer].[Gender] is applied as the axis. The counts in the crosstab refer to Customers. The crosstab is said to be "Resolved at the Customer level."
Dim oTable as ITable
Dim oXTab as ICrosstab
Dim oSession as ISession
Set oTable = oSession.Object("[Demo].[Customer]")
Set oXTab = oTable.NewCrosstab
...
In the diagram below, the resolution level is "Customer Table". Each cell therefore relates to counts of customers. From this it can be seen that there are 16 females who live in a Bungalow.
For simple crosstabs where the axes come from the same table as the Resolution Table, the results are straightforward. However, once data from different tables starts to be combined, there are subtle differences in result dependent on exactly how the crosstab is constructed.
Domain
In Engine, selection results and domains are "Owned" by a table. This is referred to as either the "Resolution Level" or "Owner Table" of the domain.
The selection count obtained from a domain (and also its underlying recordset) will always relate back to the number of records in the Resolution Table of the domain.
The Resolution level of a domain is determined by the query that is executed and the underlying database Schema. By default, when a query is executed, the Resolution Level of the domain will be that on the MANY side of a [ONE to MANY] link. If the link is [ONE to ONE], the resolution level will be the table with the most records.
Consider the following data structure and query:
SELECT* FROM Customer, Transaction;
By default, the Resolution Level of the domain that contains the selection results will be the Transactions table. The count of records in the domain is the number of records in the Transactions table that are in the selection (in this case, all of them)
Manipulating Domains
When manipulating domains (i.e., combining one domain with another) an understanding of resolution levels is key to ensuring that the results obtained are those required.
Consider the following case. 2 domains are created;
- Dom1 - All transactions for product A : Select * from Transactions where ProductID='A';
- Dom2 - All customers who are female : Select * from Customer where Gender='F';
The Resolution level of Domain 1 is TRANSACTIONS.
The Resolution level of Domain 2 is CUSTOMER.
Example 1
Domain 1 is ANDed to Domain 2;
Dim oDom1 as IDomain
Dim oDom2 as IDomain
...
Dom2.AndDomain Dom1.Handle
...
The Resolution level is now that of Domain 2 - CUSTOMER. Dom2 now refers to all female CUSTOMERS who bought Product A.
Example 2
However, if Domain 2 is ANDed to Domain 1, a different result is obtained;
Dim oDom1 as IDomain
Dim oDom2 as IDomain
...
Dom1.AndDomain Dom2.Handle
...
The resolution level is now that of Domain 1 - TRANSACTIONS. Dom1 now refers to all TRANSACTIONS where Product A was bought by females.
Queries
When executing a query, it is possible to execute two almost identical queries and obtain different answers, simply by modifying the resolution level of the result-set.
In a 2 table NSQL query, the resolution level of a query defaults to the MANY side of a ONE to MANY join. This can be modified by using the DistinctRow keyword which is used to force the query to resolve to a table other than the default.
For example, the following query will return all records in the Transactions table that are linked to the customer table;
SELECT * FROM Customer, Transactions;
However, this next query will return all Customers who have a linked transaction record;
SELECT DistinctRow Customer.*, Transactions.* FROM Customer, Transactions;
